import json
from typing import Optional, List, Dict, Any
from scorpio.core.common import get_logger
from scorpio.core.database import VectorDatabaseManager

logger = get_logger(__name__)

class QueryHistoryProcessor:
    def __init__(self, db_manager: VectorDatabaseManager, dimension: int):
        logger.debug("initialize QueryHistoryProcessorService")
        self.db_manager = db_manager
        self.dimension = dimension
    
    async def create_table(self):
        """创建查询历史表"""
        async with self.db_manager.get_connection() as conn:
            await conn.execute('''
                CREATE TABLE IF NOT EXISTS query_history (
                    id BIGSERIAL PRIMARY KEY,
                    question TEXT NOT NULL,
                    answer TEXT,
                    retrieved_context JSONB,
                    context_count INTEGER,
                    user_id TEXT,
                    session_id TEXT,
                    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
                    response_time_ms INTEGER
                );
            ''')
            logger.info("query_history 表创建/检查完成")
    
    async def create_indexes(self):
        """创建查询历史表的索引"""
        async with self.db_manager.get_connection() as conn:
            await conn.execute('''
                CREATE INDEX IF NOT EXISTS idx_query_history_created_at 
                ON query_history(created_at);
            ''')
            logger.info("query_history 表索引创建/检查完成")
    
    async def insert(self, question: str, answer: Optional[str] = None, 
                            retrieved_context: Optional[List[Dict[str, Any]]] = None, 
                            context_count: Optional[int] = None, user_id: Optional[str] = None, 
                            session_id: Optional[str] = None, response_time_ms: Optional[int] = None) -> int:
        """插入查询历史记录"""
        async with self.db_manager.get_connection() as conn:
            query_id = await conn.fetchval('''
                INSERT INTO query_history (question, answer, 
                        retrieved_context, context_count, user_id, 
                        session_id, response_time_ms)
                VALUES ($1, $2, $3, $4, $5, $6, $7)
                RETURNING id;
            ''', question, answer, json.dumps(retrieved_context) if retrieved_context else None, 
            context_count, user_id, session_id, response_time_ms)
            return query_id
    
    async def get(self, user_id: Optional[str] = None, session_id: Optional[str] = None, 
                        limit: int = 100, offset: int = 0) -> List[Dict[str, Any]]:
        """获取查询历史记录"""
        async with self.db_manager.get_connection() as conn:
            query = '''
                SELECT id, question, answer, retrieved_context, context_count, 
                        user_id, session_id, created_at, response_time_ms
                FROM query_history
            '''
            params = []
            
            if user_id or session_id:
                query += ' WHERE '
                conditions = []
                if user_id:
                    conditions.append(f'user_id = ${len(params) + 1}')
                    params.append(user_id)
                if session_id:
                    conditions.append(f'session_id = ${len(params) + 1}')
                    params.append(session_id)
                
                query += ' AND '.join(conditions)
            
            query += ' ORDER BY created_at DESC LIMIT $' + str(len(params) + 1) + ' OFFSET $' + str(len(params) + 2)
            params.extend([limit, offset])
            
            results = await conn.fetch(query, *params)
            return [dict(result) for result in results]
    
    async def delete(self, query_id: int) -> bool:
        """删除查询历史记录"""
        async with self.db_manager.get_connection() as conn:
            result = await conn.execute('''
                DELETE FROM query_history WHERE id = $1;
            ''', query_id)
            return result == 'DELETE 1'
    
    async def delete_all(self, user_id: Optional[str] = None) -> int:
        """删除所有查询历史记录，可选按用户ID过滤"""
        async with self.db_manager.get_connection() as conn:
            if user_id:
                result = await conn.execute('''
                    DELETE FROM query_history WHERE user_id = $1;
                ''', user_id)
            else:
                result = await conn.execute('''
                    DELETE FROM query_history;
                ''')
            
            return int(result.split()[1]) if result else 0